﻿﻿using System;
using System.Data;
using System.IO;
//using NPOI.HSSF.UserModel;
//using NPOI.SS.UserModel;
//using NPOI.XSSF.UserModel;
using System.Web;

namespace Light.Utility
{
    /// <summary>
    /// 基于NPOI的Excel读写操作类
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 将Excel文件读入DataSet
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataSet ReadToDataSet(string filePath)
        {
            NPOI.SS.UserModel.IWorkbook book = null;
            DataSet ds = new DataSet();

            #region 初始化 book
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    if (filePath.ToLower().EndsWith(".xls"))
                    {
                        book = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
                    }
                    else
                    {
                        book = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
                    }
                }
            }
            catch (Exception e)
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    book = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
                }
            }
            #endregion

            #region 设置DataSet

            for (int s = 0; s < book.NumberOfSheets; s++)
            {
                NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(s);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                DataTable dt = new DataTable();

                int rowidx = 0;
                while (rows.MoveNext())
                {
                    if (rowidx == 0) //读取列名
                    {
                        NPOI.SS.UserModel.IRow row = sheet.GetRow(rowidx++);
                        if (row == null && ds.Tables.Count == 0)
                            throw new Exception("请设置第一行为列名");

                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            string columnName = StringHelper.ToString(row.GetCell(j));
                            if (String.IsNullOrEmpty(columnName))
                                columnName = "Column" + j;
                            dt.Columns.Add(columnName);
                        }
                    }
                    else //读取数据行
                    {
                        NPOI.SS.UserModel.IRow row = sheet.GetRow(rowidx++);
                        if (row == null)
                            continue;

                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {

                            NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                            if (cell == null)
                            {
                                dr[i] = null;
                            }
                            else
                            {
                                dr[i] = cell.ToString();
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
                ds.Tables.Add(dt);
            }
            #endregion

            return ds;
        }

        /// <summary>
        /// 将Excel文件读入DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataTable ReadToDataTable(string filePath)
        {
            DataSet ds = ReadToDataSet(filePath);
            return ds == null ? null : ds.Tables[0];
        }

        /// <summary>
        /// 将DataSet下载到客户端
        /// </summary>
        /// <param name="ds">DataTable数据源</param>
        /// <param name="fileName">文件名（含扩展名）为空则取第一个tableName,还为空则取日期</param>
        public static void ResponseWrite(DataSet ds, string fileName)
        {
            using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
            {
                if (String.IsNullOrEmpty(fileName))
                    fileName = ds.Tables[0].TableName;
                if (String.IsNullOrEmpty(fileName))
                    fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff");

                if (!fileName.ToLower().EndsWith(".xls") && !fileName.ToLower().EndsWith(".xlsx"))
                    fileName += ".xls";

                NPOI.SS.UserModel.IWorkbook book = CreateIWorkbook(fileName);

                WriteToBook(book, ds);
                book.Write(ms);

                WebHelper.CurContext.Response.Clear();
                WebHelper.CurContext.Response.ClearContent();
                //WebHelper.CurContext.Response.ClearHeaders();
                WebHelper.CurContext.Response.ContentType = "application/vnd.ms-excel";
                WebHelper.CurContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", HttpUtility.UrlEncode(fileName)));
                WebHelper.CurContext.Response.BinaryWrite(ms.ToArray());
                WebHelper.CurContext.Response.Flush();

                book = null;
                ms.Close();
                ms.Dispose();
            }
        }

        /// <summary>
        /// 将DataTable下载到客户端
        /// </summary>
        /// <param name="dt">DataTable数据源</param>
        /// <param name="fileName">文件名（含扩展名）为空则取第一个tableName,还为空则取日期</param>
        public static void ResponseWrite(DataTable dt, string fileName)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(dt.Copy());
            ResponseWrite(ds, fileName);
        }

        /// <summary>
        /// 将DataSet写入Excel文件
        /// </summary>
        /// <param name="ds">数据源</param>
        /// <param name="filePath">文件保存的完整物理路径名</param>
        public static void SaveToFile(DataSet ds, string filePath)
        {
            using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
            {
                NPOI.SS.UserModel.IWorkbook book = CreateIWorkbook(filePath);
                WriteToBook(book, ds);
                book.Write(ms);

                using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
                book = null;
            }
        }

        /// <summary>
        /// 将DataTalble写入Excel文件
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="filePath">文件保存的完整物理路径名</param>
        public static void SaveToFile(DataTable dt, string filePath)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            SaveToFile(ds, filePath);
        }

        /// <summary>
        /// 根据文件(扩展)名创建对应的IWorkbook
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private static NPOI.SS.UserModel.IWorkbook CreateIWorkbook(string fileName)
        {
            NPOI.SS.UserModel.IWorkbook book = null;
            if (fileName.ToLower().EndsWith(".xlsx"))
                book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            else
                book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            return book;
        }

        /// <summary>
        /// 将DataSet写入IWorkbook对象
        /// </summary>
        /// <param name="book">写入目标对象</param>
        /// <param name="ds">数据源</param>
        private static void WriteToBook(NPOI.SS.UserModel.IWorkbook book, DataSet ds)
        {
            foreach (DataTable dt in ds.Tables)
            {
                WriteToBook(book, dt);
            }

            //return book;
        }

        /// <summary>
        /// 将DataTable写入IWorkbook对象
        /// </summary>
        /// <param name="book">写入目标对象</param>
        /// <param name="dt">数据源</param>
        /// <returns></returns>
        private static void WriteToBook(NPOI.SS.UserModel.IWorkbook book, DataTable dt)
        {
            if (null != dt && dt.Rows.Count > 0)
            {
                if (String.IsNullOrEmpty(dt.TableName))
                {
                    dt.TableName = "TableName1";
                }

                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
                NPOI.SS.UserModel.IRow header = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    header.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                    sheet.AutoSizeColumn(i);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        object value = dt.Rows[i][j];
                        if (value != null && (StringHelper.IsMobileNumber(value) || StringHelper.IsIDCardNumber(value) || value.ToString().Length > 10))
                            dataRow.CreateCell(j).SetCellValue(value.ToString());
                        else if (StringHelper.IsNumeric(value))
                            dataRow.CreateCell(j).SetCellValue(StringHelper.ToDouble(dt.Rows[i][j]));
                        else if (StringHelper.IsDateTime(value))
                            dataRow.CreateCell(j).SetCellValue(StringHelper.ToDateTime(dt.Rows[i][j]).ToString("yyyy-MM-dd"));
                        else
                            dataRow.CreateCell(j).SetCellValue(StringHelper.ToString(dt.Rows[i][j]));
                    }
                }

            }
        }

    }
}
